package com.walter {
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;

	public class SQLConn {
		public var conn:SQLConnection;
		private var initComplete:Boolean = false;
		public var sqlStat:SQLStatement;
		
		public function SQLConn() {}

		public function open(dbName:String):void {
			conn = new SQLConnection();
			conn.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
		    conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			var dbFile:File = File.applicationStorageDirectory.resolvePath(dbName)  
			// open database,If the file doesn't exist yet, it will be created
			conn.openAsync(dbFile);
		}
  
		// connect and init database/table
		private function onDatabaseOpen(event:SQLEvent):void {
			// init sqlStatement object
			sqlStat = new SQLStatement();
			sqlStat.sqlConnection = conn;
			sqlStat.text = "CREATE TABLE IF NOT EXISTS walter_feedings (" +
						   "    id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                           "    start DATE, " +
                           "    end DATE, " +
                           "    finish_breast text, " +
                           "    notes TEXT )";
			sqlStat.addEventListener(SQLEvent.RESULT, statResult);
			sqlStat.addEventListener(SQLErrorEvent.ERROR, createError);
			sqlStat.execute();                     

			sqlStat = new SQLStatement();
			sqlStat.sqlConnection = conn;			
			sqlStat.text   = "CREATE TABLE IF NOT EXISTS walter_diaper_changes (" +
						     "    id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                             "    discover DATE, " +
                             "    poop TEXT, " +
                             "    pee  TEXT, " +
                             "    notes TEXT )";
			sqlStat.addEventListener(SQLEvent.RESULT, statResult);
			sqlStat.addEventListener(SQLErrorEvent.ERROR, createError);
			sqlStat.execute();
			
			sqlStat = new SQLStatement();
			sqlStat.sqlConnection = conn;			
			sqlStat.text   = "CREATE TABLE IF NOT EXISTS walter_biliblanket_flips (" +
						     "    id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                             "    start DATE, " +
                             "    end DATE, " +
                             "    side TEXT, " +
                             "    notes TEXT )";
			sqlStat.addEventListener(SQLEvent.RESULT, statResult);
			sqlStat.addEventListener(SQLErrorEvent.ERROR, createError);
			sqlStat.execute();            			               			                     
		}
		
		private function statResult(event:SQLEvent):void {
			// refresh data
			var sqlresult:SQLResult = sqlStat.getResult();   
			if(sqlresult != null) {                          
				if(sqlresult.data == null){
					return;                                     
				}
			}
		}
		private function createError(event:SQLErrorEvent):void {
			//trace("Error code:", event.error.code);
			trace("Details:", event.error.message);
		}
		
		private function errorHandler(event:SQLErrorEvent):void {
			//trace("Error code:", event.error.code);
			trace("Details:", event.error.message);
		}

		public function executeQuery(sql:String, onComplete:Function):void {
			sqlStat = new SQLStatement();
			sqlStat.sqlConnection = conn;
			sqlStat.text = sql;
			sqlStat.addEventListener(SQLEvent.RESULT, onComplete);
			sqlStat.execute();
		}                     		
	}
}